import json

from geoalchemy2 import Geometry
from app.extensions import db
from sqlalchemy import select

class Address(db.Model):
    __tablename__ = 'address'

    houseid = db.Column(db.Integer, db.ForeignKey('houseinfo.houseid'),primary_key=True,nullable=False,comment='房源ID（关联houseinfo表）')
    province=db.Column(
        db.String(255),
        nullable=True,
        comment='省份'
    )
    city=db.Column(
        db.String(255),
        nullable=True,
        comment='城市'
    )
    district=db.Column(
        db.String(255),
        nullable=True,
        comment='区/县'
    )
    street=db.Column(
        db.String(255),
        nullable=True,
        comment='街道地址'
    )
    location=db.Column(
        Geometry('POINT'),
        nullable=True,
        comment='地理坐标点'
    )

    #定义与Houseinfo的一对一关系
    houseinfo=db.relationship(
        'HouseInfo',
        backref=db.backref('address',uselist=False),
        foreign_keys=[houseid],
    )

    def __repr__(self):
        return f'<Address {self.houseid}:{self.province}{self.city}{self.district}>'

    def to_dict(self):
        """将地址信息转为字典，包含标准GeoJSON格式的地理坐标"""
        location_data = None
        if self.location:
            try:
                # 直接对几何字段调用 ST_AsGeoJSON（无需先转换WKB）
                geo_json = db.session.scalar(
                    select(db.func.ST_AsGeoJSON(Address.location))
                    .where(Address.houseid == self.houseid)
                )
                location_data = json.loads(geo_json) if geo_json else None
            except Exception as e:
                print(f"GeoJSON转换错误: {e}")
                location_data = None

        return {
            'houseid': self.houseid,
            'province': self.province,
            'city': self.city,
            'district': self.district,
            'street': self.street,
            'location': location_data  # 格式: {"type": "Point", "coordinates": [116.404, 39.915]}
        }

    @classmethod
    def updatable_fields(cls):
        return ['province', 'city', 'district', 'street', 'location']